package hr.koshnica.android.database;

import java.util.HashMap;

import hr.koshnica.android.database.KoshnicaContract.EventsColumns;
import hr.koshnica.android.database.KoshnicaContract.LocationsColumns;
import hr.koshnica.android.database.KoshnicaContract.SearchColumns;
import android.app.SearchManager;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import android.util.Log;

public class KoshnicaDatabase {

	private static final String TAG = "DataBaseAdapter";		
	 
	private static final String DATABASE_NAME = "data";
	
	private static final int DATABASE_VERSION = 2;

	private final Context mContext;
	
	private DatabaseHelper mDataBaseHelper;
	
	public SQLiteDatabase mDataBase;
	 
	
	public interface Tables {
	
		String LOCATIONS = "locations";

		String EVENTS = "events";
		
		String SEARCH = "searchtable";
		
	}
	
	private static final String CREATE_LOCATIONS_TABLE = "create table "
            + Tables.LOCATIONS + " ("
            + BaseColumns._ID + " integer primary key autoincrement, "
            + LocationsColumns.LOC_NAME + " text not null, "
            + LocationsColumns.LOC_DESC + " text not null, " 
            + LocationsColumns.LOC_GENRE + " text not null, " 
            + LocationsColumns.LOC_STATUS + " text not null, " 
            + LocationsColumns.LOC_LIKE + " integer not null);";
	
    private static final String CREATE_EVENTS_TABLE = "create table "
            + Tables.EVENTS + " ("
            + BaseColumns._ID + " integer primary key autoincrement, "
            + EventsColumns.EVENT_NAME + " text not null, " 
            + EventsColumns.EVENT_DESC + " text not null, " 
            + EventsColumns.EVENT_GENRE + " text not null, " 
            + EventsColumns.EVENT_STATUS + " text not null, " 
            + EventsColumns.EVENT_LIKE + " integer not null, " 
            + EventsColumns.EVENT_PRICE + " integer not null, "
            + EventsColumns.EVENT_CLUB_NAME + " text not null);";
    
    private static final String CREATE_SEARCH_TABLE = "create virtual table "
            + Tables.SEARCH + " using fts3(" + SearchColumns.TITLE + " text, "
            + SearchColumns.OVERVIEW + " text, " 
            + SearchColumns.DATA_ID + " integer, " 
            + SearchColumns.EXTRA_DATA + " text" + ");";
    
    
    private static final HashMap<String,String> mColumnMap = buildColumnMap();

    private static HashMap<String,String> buildColumnMap() {
        HashMap<String,String> map = new HashMap<String,String>();
        map.put(SearchColumns.TITLE, SearchManager.SUGGEST_COLUMN_TEXT_1);
        map.put(SearchColumns.OVERVIEW, SearchManager.SUGGEST_COLUMN_TEXT_2);
        map.put(BaseColumns._ID, "rowid AS " +
                BaseColumns._ID);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
        map.put(SearchManager.SUGGEST_COLUMN_INTENT_EXTRA_DATA,
                SearchManager.SUGGEST_COLUMN_INTENT_EXTRA_DATA);
        
        
    
        return map;
    }
    
	
	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
	    }

		@Override
	    public void onCreate(SQLiteDatabase db) {

	        db.execSQL(CREATE_LOCATIONS_TABLE);
	        db.execSQL(CREATE_EVENTS_TABLE);
	        db.execSQL(CREATE_SEARCH_TABLE);
		}

		@Override
	    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {	 
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");

			db.execSQL("DROP TABLE IF EXISTS " + Tables.LOCATIONS);            
			db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS);	            
			db.execSQL("DROP TABLE IF EXISTS " + Tables.SEARCH);
	        onCreate(db);        
		}
	}
	
	public DatabaseHelper getDatabaseHelper() {
        return mDataBaseHelper;
    }
	
	public KoshnicaDatabase(Context ctx) {
        this.mContext = ctx;
    }
	
	public KoshnicaDatabase open() throws SQLException {

        mDataBaseHelper = new DatabaseHelper(mContext);
        
        mDataBase = mDataBaseHelper.getWritableDatabase();
                
        return this;
    }
	
	public void close() {
        mDataBaseHelper.close();
    }
	
	public long insertInput(String tableName, String inputName, String inputDesc, 
            String inputGenre, String inputStatus, int inputLike, int inputPrice, 
            String inputClubName) {
        Log.i(TAG, "In Create Input");
        ContentValues initialValues = new ContentValues();
        long result = 0;
        
        if (tableName.equals(Tables.LOCATIONS)) {
            initialValues.put(LocationsColumns.LOC_NAME, inputName);
            initialValues.put(LocationsColumns.LOC_DESC, inputDesc);
            initialValues.put(LocationsColumns.LOC_GENRE, inputGenre);
            initialValues.put(LocationsColumns.LOC_STATUS, inputStatus);
            initialValues.put(LocationsColumns.LOC_LIKE, inputLike);
            result = mDataBase.insert(Tables.LOCATIONS, null, initialValues);
            fillSearchTable();
            
            return result;
        } else if (tableName.equals(Tables.EVENTS)) {
            initialValues.put(EventsColumns.EVENT_NAME, inputName);
            initialValues.put(EventsColumns.EVENT_DESC, inputDesc);
            initialValues.put(EventsColumns.EVENT_GENRE, inputGenre);
            initialValues.put(EventsColumns.EVENT_STATUS, inputStatus);
            initialValues.put(EventsColumns.EVENT_LIKE, inputLike);
            initialValues.put(EventsColumns.EVENT_PRICE, inputPrice);
            initialValues.put(EventsColumns.EVENT_CLUB_NAME, inputClubName);
            result = mDataBase.insert(Tables.EVENTS, null, initialValues);
            fillSearchTable();
            
            return result;
        } else
            return -1;
    }
	
	public long insertInput(String tableName, ContentValues values) {
        Log.i(TAG, "In Create Input");
        
        long result = 0;
        
        if (tableName.equals(Tables.LOCATIONS)) {          
            result = mDataBase.insert(Tables.LOCATIONS, null, values);
            fillSearchTable();           
            return result;
        } else if (tableName.equals(Tables.EVENTS)) {          
            result = mDataBase.insert(Tables.EVENTS, null, values);
            fillSearchTable();            
            return result;
        } else
            return -1;
    }
    
    // Brisanje jedne lokacije.
    public int deleteInput(String tableName, long inputId) {
        int result;
        if (tableName.equals(Tables.LOCATIONS)){
            result = mDataBase.delete(tableName, BaseColumns._ID + "=" + inputId, null);
            fillSearchTable();
            return result;
        } else if (tableName.equals(Tables.EVENTS)){            
            result = mDataBase.delete(tableName, BaseColumns._ID + "=" + inputId, null);
            fillSearchTable();
            return result;
        } else
            return -1;
    }

    public int deleteInput(String tableName, String selection, String[] selectionArgs) {
        int result;
        if (tableName.equals(Tables.LOCATIONS)){
            result = mDataBase.delete(tableName, selection, selectionArgs);
            fillSearchTable();
            return result;
        } else if (tableName.equals(Tables.EVENTS)){            
            result = mDataBase.delete(tableName, selection, selectionArgs);
            fillSearchTable();
            return result;
        } else
            return -1;
    }
    
    // Modificiranje unosa sa vrijednoscu polja "_loc_id" == locId
    public int updateInput(String tableName, String selection, String[] selectionArgs,
            String inputName, String inputDesc, String inputGenre, 
            String inputStatus, int inputLike, int inputPrice, 
            String inputClubName) {
        int result;
        ContentValues args = new ContentValues();
        
        if (tableName.equals(Tables.LOCATIONS)) {           
            args.put(LocationsColumns.LOC_NAME, inputName);
            args.put(LocationsColumns.LOC_DESC, inputDesc);
            args.put(LocationsColumns.LOC_GENRE, inputGenre);
            args.put(LocationsColumns.LOC_STATUS, inputStatus);
            args.put(LocationsColumns.LOC_LIKE, inputLike);
            result = mDataBase.update(Tables.LOCATIONS, args, selection, selectionArgs);
            fillSearchTable();
            return result;
            
        } else if (tableName.equals(Tables.EVENTS)) {           
            args.put(EventsColumns.EVENT_NAME, inputName);
            args.put(EventsColumns.EVENT_DESC, inputDesc);
            args.put(EventsColumns.EVENT_GENRE, inputGenre);
            args.put(EventsColumns.EVENT_STATUS, inputStatus);
            args.put(EventsColumns.EVENT_LIKE, inputLike);
            args.put(EventsColumns.EVENT_PRICE, inputPrice);
            args.put(EventsColumns.EVENT_CLUB_NAME, inputClubName);
            result = mDataBase.update(Tables.EVENTS, args, selection, selectionArgs);
            fillSearchTable();
            return result;
            
        } else
            return -1;
    }
    
    
    public int updateInput(String tableName, String selection, String[] selectionArgs, ContentValues values) {
        
    	int result;
        
        if (tableName.equals(Tables.LOCATIONS)) {
            result = mDataBase.update(Tables.LOCATIONS, values, selection, selectionArgs);
            fillSearchTable();
            return result;
            
        } else if (tableName.equals(Tables.EVENTS)) {            
            result = mDataBase.update(Tables.EVENTS, values, selection, selectionArgs);
            fillSearchTable();
            return result;
            
        } else
            return -1;
    }


    // Modificiranje unosa sa vrijednoscu polja "_loc_id" == locId
    public int updateInput(String tableName, long inputId,
            String inputName, String inputDesc, String inputGenre, 
            String inputStatus, int inputLike, int inputPrice, 
            String inputClubName) {
        int result;
        ContentValues args = new ContentValues();
        
        if (tableName.equals(Tables.LOCATIONS)) {           
            args.put(LocationsColumns.LOC_NAME, inputName);
            args.put(LocationsColumns.LOC_DESC, inputDesc);
            args.put(LocationsColumns.LOC_GENRE, inputGenre);
            args.put(LocationsColumns.LOC_STATUS, inputStatus);
            args.put(LocationsColumns.LOC_LIKE, inputLike);
            result = mDataBase.update(Tables.LOCATIONS, args, BaseColumns._ID + "="
                    + inputId, null);
            fillSearchTable();
            return result;
            
        } else if (tableName.equals(Tables.EVENTS)) {           
            args.put(EventsColumns.EVENT_NAME, inputName);
            args.put(EventsColumns.EVENT_DESC, inputDesc);
            args.put(EventsColumns.EVENT_GENRE, inputGenre);
            args.put(EventsColumns.EVENT_STATUS, inputStatus);
            args.put(EventsColumns.EVENT_LIKE, inputLike);
            args.put(EventsColumns.EVENT_PRICE, inputPrice);
            args.put(EventsColumns.EVENT_CLUB_NAME, inputClubName);
            result = mDataBase.update(Tables.EVENTS, args, BaseColumns._ID
                    + "=" + inputId, null);
            fillSearchTable();
            return result;
            
        } else
            return -1;
    }
    
    
    public int updateInput(String tableName, long inputId,
            ContentValues values) {
        int result;
        
        if (tableName.equals(Tables.LOCATIONS)) {
            result = mDataBase.update(Tables.LOCATIONS, values, BaseColumns._ID + "="
                    + inputId, null);
            fillSearchTable();
            return result;
            
        } else if (tableName.equals(Tables.EVENTS)) {            
            result = mDataBase.update(Tables.EVENTS, values, BaseColumns._ID
                    + "=" + inputId, null);
            fillSearchTable();
            return result;
            
        } else
            return -1;
    }
    // ovu metodu koristimo zato da dobijemo Cursor. Cursor sluzi za iteration
    // kroz
    // result koji se vrati kad posaljemo query u bazu podataka.
    public Cursor queryInputs(String tableName, String[] projectionColumns, 
    		String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
    	
    	if (tableName.equals(Tables.LOCATIONS))
            return mDataBase.query(Tables.LOCATIONS, projectionColumns, 
                    selection, selectionArgs, groupBy, having, orderBy);
        else if (tableName.equals(Tables.EVENTS))
        	return mDataBase.query(Tables.EVENTS, projectionColumns, 
                    selection, selectionArgs, groupBy, having, orderBy);
        else
            return null;
    }
    
    public Cursor fetchAllInputs(String tableName) {

        if (tableName.equals(Tables.LOCATIONS))
            return mDataBase.query(Tables.LOCATIONS, new String[] {
            		BaseColumns._ID, LocationsColumns.LOC_NAME,
                    LocationsColumns.LOC_DESC, LocationsColumns.LOC_GENRE, 
                    LocationsColumns.LOC_STATUS, LocationsColumns.LOC_LIKE }, 
                    null, null, null, null, null);
        else if (tableName.equals(Tables.EVENTS))
            return mDataBase.query(Tables.EVENTS, new String[] {
            		BaseColumns._ID, EventsColumns.EVENT_NAME,
            		EventsColumns.EVENT_DESC, EventsColumns.EVENT_GENRE, 
            		EventsColumns.EVENT_STATUS, EventsColumns.EVENT_LIKE, 
            		EventsColumns.EVENT_PRICE, EventsColumns.EVENT_CLUB_NAME }, 
            		null, null, null, null, null);
        else
            return null;
    }
    
    public Cursor fetchAllInputs(String tableName, String[] projectionColumns) {

        if (tableName.equals(Tables.LOCATIONS))
            return mDataBase.query(Tables.LOCATIONS, projectionColumns, 
                    null, null, null, null, null);
        else if (tableName.equals(Tables.EVENTS))
            return mDataBase.query(Tables.EVENTS, projectionColumns, 
            		null, null, null, null, null);
        else
            return null;
    }

    // Vraca kursor koji je pozicioniran na Lokaciju sa zadanim LocId-em
    public Cursor fetchInput(String tableName, long inputId)
            throws SQLException {

        Cursor mCursor = null;

        if (tableName.equals(Tables.LOCATIONS)) {
            mCursor = mDataBase.query(true, Tables.LOCATIONS, new String[] { 
            		BaseColumns._ID, LocationsColumns.LOC_NAME,          		
            		LocationsColumns.LOC_DESC, LocationsColumns.LOC_GENRE, 
            		LocationsColumns.LOC_STATUS, LocationsColumns.LOC_LIKE }, 
            		BaseColumns._ID + "=" + inputId,
                    null, null, null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        } else if (tableName.equals(Tables.EVENTS)) {
            mCursor = mDataBase.query(true, Tables.EVENTS, new String[] {
            		BaseColumns._ID, EventsColumns.EVENT_NAME,
            		EventsColumns.EVENT_DESC, EventsColumns.EVENT_GENRE, 
            		EventsColumns.EVENT_STATUS, EventsColumns.EVENT_LIKE, 
            		EventsColumns.EVENT_PRICE, EventsColumns.EVENT_CLUB_NAME }, 
                    BaseColumns._ID + "=" + inputId,
                    null, null, null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        } else
            return mCursor;

    }

    public Cursor fetchInput(String tableName, String[] projectionColumns, long inputId)
            throws SQLException {

        Cursor mCursor = null;

        if (tableName.equals(Tables.LOCATIONS)) {
            mCursor = mDataBase.query(true, Tables.LOCATIONS, projectionColumns,           		
            		BaseColumns._ID + "=" + inputId,
            		null, null, null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        } else if (tableName.equals(Tables.EVENTS)) {
            mCursor = mDataBase.query(true, Tables.EVENTS, projectionColumns, 
                    BaseColumns._ID + "=" + inputId,
                    null, null, null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        } else
            return mCursor;

    }
    
    public void deleteTables() {
        mDataBase.execSQL("DROP TABLE IF EXISTS " + Tables.LOCATIONS);
        mDataBase.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS);
        mDataBase.execSQL("DROP TABLE IF EXISTS " + Tables.SEARCH);        
        mDataBase.execSQL(Tables.LOCATIONS);
        mDataBase.execSQL(Tables.EVENTS);
        mDataBase.execSQL(Tables.SEARCH);
    }
    
    public long addSearchSuggestion(String word, String definition, String path, int id) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(SearchColumns.TITLE , word);
        initialValues.put(SearchColumns.OVERVIEW, definition); 
        System.out.println(" aSS: "+id);
        initialValues.put(SearchColumns.DATA_ID , id);
        initialValues.put(SearchColumns.EXTRA_DATA , path);

        return mDataBase.insert(Tables.SEARCH, null, initialValues);
    }
        
    public Cursor getSearchMatches(String query, String[] columns) {
        String selection = SearchColumns.TITLE  + " MATCH ?";
        String[] selectionArgs = new String[] {query+"*"};

        return querySearchTable(selection, selectionArgs, columns);
    }
    
    private Cursor querySearchTable(String selection, String[] selectionArgs, String[] columns) {
        /* The SQLiteBuilder provides a map for all possible columns requested to
         * actual columns in the database, creating a simple column alias mechanism
         * by which the ContentProvider does not need to know the real column names
         */
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(Tables.SEARCH);
        builder.setProjectionMap(mColumnMap);
        Cursor cursor = builder.query(mDataBase, columns, selection, selectionArgs, null, null, null);

        if (cursor == null) {
            return null;
        } else if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }
        return cursor;
        
    }
        
    private void fillSearchTable(){
              	
        mDataBase.execSQL("DROP TABLE IF EXISTS " + Tables.SEARCH);
        mDataBase.execSQL(CREATE_SEARCH_TABLE);
            
       Cursor crs = fetchAllInputs(Tables.LOCATIONS, 
    		   new String[] {BaseColumns._ID, LocationsColumns.LOC_NAME, LocationsColumns.LOC_DESC});
            
       while (crs.moveToNext()){
    	   String word = crs.getString(crs.getColumnIndex(LocationsColumns.LOC_NAME));
           String definition = crs.getString(crs.getColumnIndex(LocationsColumns.LOC_DESC));
           String path = KoshnicaContract.PATH_LOCATIONS + "/" + crs.getString(crs.getColumnIndex(BaseColumns._ID));;
           System.out.println(path);
           int id = crs.getInt(crs.getColumnIndex(BaseColumns._ID));
           
           addSearchSuggestion(word, definition, path, id);
       }
            
       crs = fetchAllInputs(Tables.EVENTS,
    		   new String[] {BaseColumns._ID, EventsColumns.EVENT_NAME, EventsColumns.EVENT_DESC});
            
       while (crs.moveToNext()){       
    	   String word = crs.getString(crs.getColumnIndex(EventsColumns.EVENT_NAME));
           String definition = crs.getString(crs.getColumnIndex(EventsColumns.EVENT_DESC));
           String path = KoshnicaContract.PATH_EVENTS  + "/" + crs.getString(crs.getColumnIndex(BaseColumns._ID));
           System.out.println(path);
           int id = crs.getInt(crs.getColumnIndex(BaseColumns._ID));
           addSearchSuggestion(word, definition, path, id);
       }
    }
}
